# coding=utf-8

############################################
# 脚本名称 : 经济运行部经济运行数据自动化运算脚本
# 作者 : 赵强、刘子路
# 版本 : 201702
############################################

# 导入基础模块
# 导入excel模块
import xlrd
import openpyxl
# 变量类型判断库
import types
# 导入OS操作库
import os
import shutil
# 时间库
import time
# 文件操作库
import glob
# 数学模块
import math
import re

################# ############################
# 基础设定
#############################################
# 字符序列
# 用于写入数据的时候，遍历值
Columns = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ']
# 所有数据存储变量
Data = {
    # 配置数据
    # eg : Data['config']['company-name'] = ...
    'config' : {},
    # 今年集团合并表数据
    # eg : Data['marge-all'][0~1 今年利润表~今年资产负债表]['C12'] = ...
    'marge-all' : {},
    # 去年集团合并表数据
    # eg : Data['marge-parent'][0~1 去年利润表~去年资产负债表]['G13'] = ...
    'marge-parent':{},
    # 各公司数据表
    # eg : Data['company']['东兴' 公司名称][0~4 经济运行表表序列]['C22'] = ...
    'company' : {},
    # 各公司合并数据表
    # eg : Data['company'][0~4 经济运行表表序列]['A32'] = ...
    'marge-company' : {},
    # 经济运行表，取最大数的表
    # eg : Data['jjyx']['E15'] = ...
    'jjyx' : {},
    # 经济运行表5板块区分，根据营业收入自动计算比例，按照板块区分
    # eg : Data['bk']['类金融']['今年营业收入'] = ...
    'bk' : {},
    # 各公司营业收入取值
    # eg : Data['yysr']['东兴' 公司名称]['本年累计'] = ...
    'yysr' : {}
}
# 错误列队
ErrorList = []
# 板块列队
bkList = []
# 公司和板块对应关系
# 如果发现营业收入全部是0，无法计算所属板块，则自动按照默认设置拆分
# 计算的时候，一般一个公司对应一个板块，但如果有两个或多个板块则自动平分计算
# eg : {'东兴':{'板块A':0.5,'板块B':0.5},'担保':1.0}
companyDefaultBks = {}

############################################
# 模块设定
############################################

# 获取excel操作句柄
def GetExcel(fileSrc):
    if fileSrc.find('~$') > -1:
        return False
    excel = xlrd.open_workbook(fileSrc)
    if not excel:
        SendError('无法读取excel文件：' + fileSrc)
    print('读取excel文件成功：' + fileSrc)
    return excel

# 根据键值获取表句柄
def GetExcelSheetKey(excel,key):
    try:
        return excel.sheet_by_index(key)
    except:
        return False

# 向错误文件发送一个错误
def SendError(message):
    print('发生一个错误：' + message)
    ErrorList.append(message)

# 将表内数据全部读出到数组中
def ReadSheetValues(sheet):
    if not sheet:
        return False
    newData = {}
    for h in range(sheet.nrows):
        for c in range(sheet.ncols):
            key = Columns[c] + str(h+1)
            newData[key] = sheet.cell(h,c).value
    return newData

# 比对一组数据，N个最大值
# d 的数据标准为 {'key':{...},...}
# 返回新的数据
# 用于计算经济运行表2数据
def ComparedData(d,n=2):
    newData = {}
    d = list.sort(d,2,True)
    s = 0
    for key in d:
        if s > n:
            break
        s += 1
        if key not in newData:
            newData[key] = {}
        newData.append(d[key])
    return newData

############################################
# 开始脚本
############################################
# 读取配置数据
excel = GetExcel('./1-基础设定/基础设置.xlsx')
print('开始读取配置文件...')
if excel:
    # 读取成功，所有列装载到配置中
    print('读取成功，准备装载配置数据...')
    sheet = GetExcelSheetKey(excel,0)
    if sheet:
        # 获取第一张表
        for i in range(sheet.nrows):
            if i < 2:
                continue
            key = sheet.cell(i,1).value
            Data['config'][key] = sheet.cell(i,2).value
    print('读取公司和板块默认数据对照表数据...')
    sheet = GetExcelSheetKey(excel,1)
    if sheet:
        # 遍历表行
        for i in range(sheet.nrows):
            key = sheet.cell(i,0).value
            value = sheet.cell(i,1).value
            values = {}
            valueSplite = value.split(',')
            companyDefaultBks[key] = {}
            s = float(1 / len(valueSplite))
            for j in valueSplite:
                companyDefaultBks[key][j] = s
    # 读取完成
    print('读取配置完成.')

# 读取集团合并表数据
print('准备读取集团合并表数据...')
excel = GetExcel('./3-集团合并/今年集团合并表.xlsx')
if excel:
    print('读取了今年集团合并表数据，准备装载数据...')
    sheet = GetExcelSheetKey(excel,0)
    if sheet:
        Data['marge-all']['0'] = ReadSheetValues(sheet)
        print('装载完成今年利润表.')
    sheet = GetExcelSheetKey(excel,1)
    if sheet:
        Data['marge-all']['1'] = ReadSheetValues(sheet)
        print('装载完成今年资产负债表.')
excel = GetExcel('./3-集团合并/去年集团合并表.xlsx')
if excel:
    print('读取了去年集团合并表数据，准备装载数据...')
    sheet = GetExcelSheetKey(excel,0)
    if sheet:
        Data['marge-parent']['0'] = ReadSheetValues(sheet)
        print('装载完成去年利润表.')
    sheet = GetExcelSheetKey(excel,1)
    if sheet:
        Data['marge-parent']['1'] = ReadSheetValues(sheet)
        print('装载完成去年资产负债表.')
print('读取完成所有集团合并表数据.')

# 读取各公司表数据
print('开始读取各公司上报数据表...')
for fileSrc in glob.glob('./2-各公司上报/*.xlsx'):
    excel = GetExcel(fileSrc)
    if not excel:
        print('无法读取表文件，可能该表是隐藏的缓冲表，表文件路径：' + fileSrc)
        continue
    print('读取' + fileSrc + '公司的数据表，准备装载数据...')
    # 获取公司简称
    fileName = os.path.basename(fileSrc)
    fileNames = fileName.split('.')
    companyKey = fileNames[0]
    # 建立数据
    Data['company'][companyKey] = {}
    # 遍历内部子表
    for sheetKey in range(5):
        sheet = GetExcelSheetKey(excel,sheetKey)
        if not sheet:
            print('数据表加载失败，无法读取该表键，表文件：' + fileSrc + '，表键 : ' + str(sheetKey))
            continue
        Data['company'][companyKey][str(sheetKey)] = ReadSheetValues(sheet)
        if not Data['company'][companyKey][str(sheetKey)]:
            print('数据表数据装载失败，请检查数据完整性或合法性，数据表来源：' + fileSrc + '文件，表键 : ' + str(sheetKey) + '。')
    print('数据加载完成.')
print('所有公司上报数据读取完成.')

# 所有公司合并到公司合并变量
print('开始合并各公司所有数据到公司合并表...')
# 上一个处理对象，用于追踪合并表错误
PrevComKey = ''
# 遍历公司数据
for comKey in Data['company']:
    # 如果不存在板块数据，则创建
    if len(bkList) < 1:
        for i in range(3, 18):
            key = 'A' + str(i)
            value = Data['company'][comKey]['4'][key]
            bkList.append(value)
            Data['bk'][value] = {}
    # 处理板块数据
    # 读取营业收入部分数据
    companyBks = {}
    companyBksSum = float(0.0)
    companyBksScale = {}
    ks = {0:'B',1:'C',2:'D',3:'E'}
    keys = {0:'营业收入本年累计',1:'营业收入上年累计',2:'营业收入本月合计',3:'营业收入上年同期'}
    for i in range(3,18):
        bkName = Data['company'][comKey]['4']['A' + str(i)]
        for j in keys:
            value = keys[j]
            if value not in Data['bk'][bkName]:
                Data['bk'][bkName][value] = float(0.0)
            if type(Data['company'][comKey]['4'][ks[j] + str(i)]) is str:
                print('错误：有一处数据不是float，并且可能无法转换，请检查数据是否合法，公司名称：' + comKey + '；表5，位置：' + ks[j] + str(i))
            Data['bk'][bkName][value] += Data['company'][comKey]['4'][ks[j] + str(i)]
        # 将营业收入本年累计单独取出，用于计算比例值
        companyBks[bkName] = Data['company'][comKey]['4'][ks[0]+str(i)]
        companyBksSum += float(Data['company'][comKey]['4'][ks[0]+str(i)])
        companyBksScale[bkName] = float(0.0)
    # 需要计算的项目定位列
    keys = {'营业成本本年累计':{0:'1',1:'B8'},'营业成本上年同期':{0:'1',1:'C8'},'利润总额本年累计':{0:'1',1:'B9'},'利润总额上年累计':{0:'1',1:'C9'},'利润总额本月合计':{0:'1',1:'D9'},'利润总额上年同期':{0:'1',1:'E9'},'货币资金期末余额':{0:'2',1:'B3'},'货币资金期初余额':{0:'2',1:'C3'}}
    # 计算公司各板块占比例
    companyBksScaleOk = False
    for i in companyBks:
        if companyBks[i] == 0.0:
            companyBksScale[i] = float(0.0)
        else:
            companyBksScale[i] = companyBks[i] / companyBksSum
            companyBksScaleOk = True
    for i in companyBks:
        # 如果公司整体营业收入不存在
        if companyBksScaleOk == False:
            if i in companyDefaultBks[comKey]:
                companyBksScale[i] = companyDefaultBks[comKey][i]
        # 计算该公司需要计算的项目
        for k in keys:
            if k not in Data['bk'][i]:
                Data['bk'][i][k] = float(0.0)
            # 数据，eg : Data['company' 公司数据组][comKey 公司][keys[k][0] keys组内指向表，针对经济运行表表序列][keys[k][1] 经济运行表位置]
            d = float(Data['company'][comKey][keys[k][0]][keys[k][1]])
            if companyBksScale[i] > 0:
                Data['bk'][i][k] += float(d * companyBksScale[i])
                if k == '营业成本本年累计':
                    print('debug 公司名称 : ' + comKey + ' , key : ' + i + ' , value : ' + str(d) + ' , s : ' + str(companyBksScale[i]))
    # 遍历公司5张表，处理其他数据
    for sheetKey in Data['company'][comKey]:
        if sheetKey not in Data['marge-company']:
            Data['marge-company'][sheetKey] = {}
        # 遍历表内容
        for key in Data['company'][comKey][sheetKey]:
            value = Data['company'][comKey][sheetKey][key]
            valueType = type(value)
            # 如果是str，则建立数据
            if valueType is str:
                Data['marge-company'][sheetKey][key] = value
                continue
            # 如果内容是int或float，则相加处理
            if key not in Data['marge-company'][sheetKey]:
                Data['marge-company'][sheetKey][key] = float(0.0)
            if valueType is int or valueType is float:
                if type(Data['marge-company'][sheetKey][key]) is str:
                    print('错误：有一处数据不是float，请检查。位置：公司合并数据数据；表序列 ' + sheetKey + '；键位 ' + key)
                    print('该错误是由于上一个表应该为float，但实际为str，导致数据不一致，所以需要改善数据表。上一个合并数据数据对象位置：公司名称 ' + PrevComKey)
                Data['marge-company'][sheetKey][key] = float(Data['marge-company'][sheetKey][key]) + float(value)
        # 专门处理表5，将营业收入内容全部抽取出来并合并，存入Data['yysr']变量
        if sheetKey == '4':
            if comKey not in Data['yysr']:
                Data['yysr'][comKey] = {}
            keys = {}
            keys[0] = Data['company'][comKey][sheetKey]['B2']
            keys[1] = Data['company'][comKey][sheetKey]['C2'] + '1'
            keys[2] = Data['company'][comKey][sheetKey]['D2']
            keys[3] = Data['company'][comKey][sheetKey]['E2'] + '2'
            ks = {0:'B',1:'C',2:'D',3:'E'}
            for key in keys:
                value = keys[key]
                if value not in Data['yysr'][comKey]:
                    Data['yysr'][comKey][value] = float(0.0)
                for i in range(3, 18):
                    k = ks[key] + str(i)
                    Data['yysr'][comKey][value] += Data['company'][comKey][sheetKey][k]
        # 专门处理表4，即sheetKey=3
        # 写入Data['jjyx']变量数据
        if sheetKey == '3':
            # 如果是第一张表，则直接写入，不需要对比
            if 'A1' not in Data['jjyx']:
                Data['jjyx'] = Data['company'][comKey][sheetKey]
                continue
            # 对比E3~15、K3~14的值，取最大值，同时将替换左侧两列数据值
            # 对比E列3~15
            for i in range(3,16):
                key = 'E' + str(i)
                key2 = 'C' + str(i)
                key3 = 'D' + str(i)
                key4 = 'F' + str(i)
                if type(Data['company'][comKey][sheetKey][key]) is str:
                    print('存在潜在错误，company : '+str(comKey)+' - sheet : '+sheetKey+' - key : ' + key +' - ' + Data['company'][comKey][sheetKey][key])
                    if Data['company'][comKey][sheetKey][key] == '':
                        Data['company'][comKey][sheetKey][key] = float(0.0)
                    else:
                        Data['company'][comKey][sheetKey][key] = float(Data['company'][comKey][sheetKey][key])
                if Data['company'][comKey][sheetKey][key] > Data['jjyx'][key]:
                    Data['jjyx'][key] = Data['company'][comKey][sheetKey][key]
                    Data['jjyx'][key2] = Data['company'][comKey][sheetKey][key2]
                    Data['jjyx'][key3] = Data['company'][comKey][sheetKey][key3]
                    Data['jjyx'][key4] = Data['company'][comKey][sheetKey][key4]
            # 对比K列3~14
            for i in range(3,15):
                key = 'K' + str(i)
                key2 = 'I' + str(i)
                key3 = 'J' + str(i)
                key4 = 'L' + str(i)
                if Data['company'][comKey][sheetKey][key] > Data['jjyx'][key]:
                    Data['jjyx'][key] = Data['company'][comKey][sheetKey][key]
                    Data['jjyx'][key2] = Data['company'][comKey][sheetKey][key2]
                    Data['jjyx'][key3] = Data['company'][comKey][sheetKey][key3]
                    Data['jjyx'][key4] = Data['company'][comKey][sheetKey][key4]
    PrevComKey = comKey


# 清空输出目录下所有xlsx文件
print('清空输出目录下所有旧文件...')
for fileSrc in glob.glob('./5-输出文件/*.xlsx'):
    os.remove(fileSrc)
print('已经将所有输出文件删除，准备载入新的输出文件。')

# 将模版放入输出文件
print('复制所有模版文件到输出文件，准备下一步完成数据输出...')
for fileSrc in glob.glob('./4-模版文件/*.xlsx'):
    excel = GetExcel(fileSrc)
    if not excel:
        continue
    onlyName = os.path.basename(fileSrc)
    newSrc = './5-输出文件/' + Data['config']['company-name'] + '-' + str(int(Data['config']['output-year'])) + '年' + str(int(Data['config']['output-month'])) + '月-' + onlyName
    if not shutil.copy(fileSrc,newSrc):
        print('无法复制模版文件.')
        continue
    print('成功将模版文件 : ' + fileSrc + '，复制到 : ' + newSrc)

# 打开输出文件，遍历所有数据
print('开始修改最终数据表...')
for fileSrc in glob.glob('./5-输出文件/*.xlsx'):
    excel = GetExcel(fileSrc)
    excelW = openpyxl.load_workbook(fileSrc)
    if not excel or not excelW:
        continue
    print('装载数据表文件：' + fileSrc)
    for sheetName in excel.sheet_names():
        sheet = excel.sheet_by_name(sheetName)
        sheetW = excelW.get_sheet_by_name(sheetName)
        if not sheet or not sheetW:
            print('无法获取数据表.')
            continue
        print('解析数据表所有键位...')
        values = ReadSheetValues(sheet)
        if not values:
            print('解析失败.')
            continue
        # 遍历所有键位，检查值，如果发现存在"|"符号，则开始处理
        for key in values:
            value = values[key]
            if not value:
                continue
            if type(value) is not str:
                continue
            if value.find('|') < 0:
                continue
            vals = value.split('|')
            if len(vals) < 1:
                continue
            # 根据vals判断Data数据，并赋予值
            if vals[0] not in Data:
                # 忽略某些特别的公式
                if vals[0] == '1-config':
                    continue
                print('模版需要的值不存在1，请确认模版位置：' + sheetName + '~' + key)
                continue
            # 不支持仅1层的嵌套
            # 支持5个层级的嵌套
            if len(vals) == 2:
                if vals[1] not in Data[vals[0]]:
                    continue
                sheetW[key] = Data[vals[0]][vals[1]]
                continue
            if vals[1] not in Data[vals[0]]:
                print('模版需要的值不存在2，请确认模版位置：' + sheetName + '~' + key)
                continue
            if len(vals) == 3:
                if vals[2] not in Data[vals[0]][vals[1]]:
                    print('模版需要的值不存在3，请确认模版位置：' + sheetName + '~' + key)
                    continue
                sheetW[key] = Data[vals[0]][vals[1]][vals[2]]
                continue
            if vals[2] not in Data[vals[0]][vals[1]]:
                print('模版需要的值不存在4，请确认模版位置：' + sheetName + '~' + key)
                continue
            if len(vals) == 4:
                if vals[3] not in Data[vals[0]][vals[1]][vals[2]]:
                    print('模版需要的值不存在5，请确认模版位置：' + sheetName + '~' + key)
                    continue
                sheetW[key] = Data[vals[0]][vals[1]][vals[2]][vals[3]]
                continue
            if vals[3] not in Data[vals[0]][vals[1]][vals[2]]:
                print('模版需要的值不存在6，请确认模版位置：' + sheetName + '~' + key)
                continue
            if len(vals) == 5:
                if vals[4] not in Data[vals[0]][vals[1]][vals[2]][vals[3]]:
                    print('模版需要的值不存在7，请确认模版位置：' + sheetName + '~' + key)
                    continue
                sheetW[key] = Data[vals[0]][vals[1]][vals[2]][vals[3]][vals[4]]
                continue
        print('解析完成.')
    excelW.save(fileSrc)
    print('保存数据到表.')

# 流程化结束
print('计算结束，请查看输出结果下的文件。')
print('本脚本由中小企业基金公司经济运行部，赵强、刘子路制作。')
time.sleep(300)
exit()